﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Linq.Expressions;
using Core.SqlExtension;
using CoreSolution.Redis.Helper;
using CoreSolution.Tools;
using Core.Model;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using System.Reflection;
using Core.Helper;



namespace Core
{
   public abstract class Data<T>:SqlProvider<T>
    {

        #region 查询
        /// <summary>
        /// 查询全部表 ToList
        /// </summary>
        /// <returns></returns>
        public virtual ListT<T> ToList()
        {
            return FormateToList<T>();
        }

        /// <summary>
        /// 查询特定字段 ToList
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <returns></returns>
        public virtual ListT<T1> ToList<T1>()
        {
            return FormateToList<T1>();
           // return null;
        }

        private ListT<T2> FormateToList<T2>()
        {
            #region 原来的dapper 的写法

            //var topNum = SetContext.TopNum;

            //var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, topNum);

            //var fromTableSql = FormatTableName();

            //var nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);

            //var whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

            //var whereSql = whereParams.SqlCmd;

            //Params = whereParams.Param;

            //var orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyExpressionList);

            //SqlString = $"{selectSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql}";

            #endregion
            
           //查询语句
           PropertyInfo[] ProPerty = SetContext.SelectType == null ? typeof(T).GetProperties() : SetContext.SelectType.GetProperties();
            var selectSql = ResolveExpression.ResolveSelect(ProPerty, SetContext.SelectExpression);

            //查询表名
            var fromTableSql = ResolveExpression.FormatTableName(typeof(T), true);

            //条件查询
            var whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
            var whereSql = whereParams.SqlCmd;

            var Params = whereParams.Param;
            //排序
            var orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyExpressionList);

            //SQL string
            string SqlString = "";
            //SQL Total
            string SqlTotalString = "";

            if (SetContext.PageSize > 0)
            {
                //查询TOP数据
                var sqlTop = ResolveExpression.ResolveTop(SetContext.PageSize);

                //统计
                SqlTotalString=$"SELECT COUNT(1) {fromTableSql}{whereSql};";

                SqlString += $@"SELECT {sqlTop}{selectSql}
            FROM    ( SELECT {selectSql}
                      ,ROW_NUMBER() OVER ( {orderbySql} ) AS ROWNUMBER
                      {fromTableSql}
                      {whereSql}
                    ) T
            WHERE   ROWNUMBER > {SetContext.SkipsNum}
                    AND ROWNUMBER <= {SetContext.SkipsNum + SetContext.PageSize};";

                /* 生成的sql 语句 带分页的
                 *SELECT COUNT(1)  FROM  [UserInfo]  WHERE ([UserName] = @UserName) ;

                   SELECT  TOP 3  [UserName] AS [UserName],[UserAge] AS [UserAge],[UserSex] AS [UserSex] 
                     FROM  ( SELECT  [UserName] AS [UserName],[UserAge] AS [UserAge],[UserSex] AS [UserSex] 
                              ,ROW_NUMBER() OVER ( ORDER BY [UserName] ASC  ) AS ROWNUMBER
                               FROM  [UserInfo] 
                               WHERE ([UserName] = @UserName) 
                            ) T
                    WHERE   ROWNUMBER > 6
                            AND ROWNUMBER <= 9 ORDER BY [UserName] ASC ;
                 *
                 */
            }
            else
            {
                SqlString = $"SELECT {selectSql} {fromTableSql}{whereSql} {orderbySql}";
            }

            //连接数据库，执行对象
            using (IDbConnection connection = new SqlConnection(base.ConnectStr))
            {
                var ListT2=connection.Query<T2>(SqlString, whereParams.Param).ToList();
                int Total = 0;
                if(!string.IsNullOrEmpty(SqlTotalString))
                Total = connection.QueryFirstOrDefault<int>(SqlTotalString, whereParams.Param);
                
                ListT<T2> list = new ListT<T2>();
                list.ListData =ListT2;
                list.Total = Total;
                list.PageSize = SetContext.PageSize;
                list.PageIndex = SetContext.PageSize==0?0:SetContext.SkipsNum / SetContext.PageSize+1;
                return list;
            }
        }


        /// <summary>
        /// 查询第一条数据
        /// </summary>
        /// <returns></returns>
        public virtual T FirstOrDefault()
        {
            //查询语句
            var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression);

            //查询表名
            var fromTableSql = ResolveExpression.FormatTableName(typeof(T),true);

            //条件查询
            var whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
            var whereSql = whereParams.SqlCmd;

            var Params = whereParams.Param;
            //排序
            var orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyExpressionList);
            
            var SqlString = $"SELECT Top 1 {selectSql} {fromTableSql}{whereSql} {orderbySql}";

            //连接数据库，执行对象
            using (IDbConnection connection = new SqlConnection(base.ConnectStr))
            {
                return connection.QuerySingleOrDefault<T>(SqlString, whereParams.Param);
            }
        }

        /// <summary>
        /// 查询第一条数据
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public virtual T FirstOrDefault(Expression<Func<T, bool>> predicate,bool IsDelete=false)
        {
            SetContext = new Core.Model.SetContext();
            //默认 去掉不要删除的数据
            if (!IsDelete)
            {
                predicate = predicate.And(CreateEqualityExpressionForIsDelete(false));
            }
            SetContext.WhereExpression = predicate;
            //查询语句 没有就是查询所有的
            var selectSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression);

            //查询表名
            var fromTableSql = ResolveExpression.FormatTableName(typeof(T),true);

            //条件查询
            var whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
            var whereSql = whereParams.SqlCmd;
            
            var SqlString = $"SELECT Top 1 {selectSql} {fromTableSql}{whereSql}";
            
            //连接数据库，执行对象
            using (IDbConnection connection = new SqlConnection(base.ConnectStr))
            {
                return connection.QuerySingleOrDefault<T>(SqlString,whereParams.Param);
            }
        }
        #endregion


        #region 添加

        /// <summary>
        /// 单条数据添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual int Add(T model)
        {
            //获取属性和值
            EntityAttribute paramsAndValues = ResolveExpression.FormatInsertParamsAndValues(model);
            //随机生成数
            int randomNum = RedisHelper.HashPollingKey("Entity_Key", "addEntity_Data");
            //存入redis hash
            RedisHelper.HashSet<EntityAttribute>("addEntity_Data", randomNum.ToString(), paramsAndValues);

            //执行添加操作
            return randomNum;
        }

        #endregion

        #region 更新
        /// <summary>
        /// 单条数据更新 全量更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual int Update(T model)
        {
            //获取属性和值
            EntityAttribute paramsAndValues = ResolveExpression.ResolveUpdateParamsAndVaules(model);
       
            //随机生成数
            int randomNum = RedisHelper.HashPollingKey("Entity_Key", "UpdateEntity_Data");
            
            //存入redis
            RedisHelper.HashSet<EntityAttribute>("UpdateEntity_Data", randomNum.ToString(), paramsAndValues);

            // //执行添加操作
            return randomNum;
        }

        /// <summary>
        /// 单条数据更新 Selector
        /// </summary>
        /// <param name="selector"></param>
        /// <returns></returns>
        public virtual int Update(Expression<Func<T, T>> selector)
        {
            //获取属性和值
            EntityAttribute paramsAndValues =ResolveExpression.ResolveUpdateSelectorParamsAndVaules(selector);
       
            //随机生成数
            int randomNum = RedisHelper.HashPollingKey("Entity_Key", "UpdateEntity_Data");

            //存入redis
            RedisHelper.HashSet<EntityAttribute>("UpdateEntity_Data", randomNum.ToString(), paramsAndValues);

            // //执行添加操作
            return randomNum;
        }

        #endregion
        

        #region 删除

        /// <summary>
        /// 单条数据delete
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual int Delete(T model)
        {
            //获取属性和值
            EntityAttribute paramsAndValues = ResolveExpression.ResolveDeleteParamsAndVaules(model);

            //随机生成数
            int randomNum = RedisHelper.HashPollingKey("Entity_Key", "deleteEntity_Data");
            
            //存入redis hash
            RedisHelper.HashSet<EntityAttribute>("deleteEntity_Data", randomNum.ToString(), paramsAndValues);

            return randomNum;
        }


        /// <summary>
        /// 单条数据delete
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual int Delete(Guid Id)
        {
            //获取Model
            var model=FirstOrDefault(CreateEqualityExpressionForId(Id));

            if (model == null) return 0;
            
            //获取属性和值
            EntityAttribute paramsAndValues = ResolveExpression.ResolveDeleteParamsAndVaules(model);

            //随机生成数
            int randomNum = RedisHelper.HashPollingKey("Entity_Key", "deleteEntity_Data");

            //存入redis hash
            RedisHelper.HashSet<EntityAttribute>("deleteEntity_Data", randomNum.ToString(), paramsAndValues);

            return randomNum;
        }

        /// <summary>
        /// 单条数据 remove
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual int Remove(Guid Id)
        {
            //获取Model
            var model = FirstOrDefault(CreateEqualityExpressionForId(Id));

            if (model == null) return 0;

            //获取属性和值
            EntityAttribute paramsAndValues = ResolveExpression.ResolveRomoveParamsAndVaules(model);

            //随机生成数
            int randomNum = RedisHelper.HashPollingKey("Entity_Key", "RemoveEntity_Data");

            //存入redis hash
            RedisHelper.HashSet<EntityAttribute>("RemoveEntity_Data", randomNum.ToString(), paramsAndValues);

            return randomNum;
        }

        /// <summary>
        /// 单条数据 remove
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual int Remove(T model)
        {
            //获取属性和值
            EntityAttribute paramsAndValues = ResolveExpression.ResolveRomoveParamsAndVaules(model);

            //随机生成数
            int randomNum = RedisHelper.HashPollingKey("Entity_Key", "RemoveEntity_Data");

            //存入redis hash
            RedisHelper.HashSet<EntityAttribute>("RemoveEntity_Data", randomNum.ToString(), paramsAndValues);

            return randomNum;
        }
        #endregion

        #region 执行命令

        /// <summary>
        /// 执行命令（待执行所有数据）
        /// </summary>
        /// <returns></returns>
        public virtual bool SaveChange()
        {
            //redis 里面取待执行所有数据

            return true;
        }

        /// <summary>
        /// 执行命令
        /// </summary>
        /// <returns></returns>
        public virtual bool SaveChange(int randomNum)
        {
            //redis 里面取待执行一条数据
           string OperType=RedisHelper.HashGet("Entity_Key", randomNum.ToString());

            if(string.IsNullOrEmpty(OperType))
            {
                //没有找到该对象
                return false;
            }

            //取出数据
           var model=RedisHelper.HashGet<EntityAttribute>(OperType, randomNum.ToString());

            //连接数据库，执行对象
            using (IDbConnection connection = new SqlConnection(base.ConnectStr))
            {
                //DynamicParameters Params = new DynamicParameters();
                //foreach(var item in model.attributeValues)
                //{
                //    Params.Add(item.Key, item.Value);
                //}
                return connection.Execute(model.attributeSqlStr, model.attributeValues) >0?true:false;
            }
        }
        #endregion
    }
}
